declare

l_xml XMLTYPE;
l_node XMLTYPE;
l_xpath varchar2(500);
l_cnt int := 0;
l_number_of_emps INT;
l_tmp_var varchar2(300);
l_dept_xml XMLTYPE;


PROCEDURE PROCESS_EMPLOYEE (p_employee_xml XMLTYPE)
IS
BEGIN

  
    dbms_output.put_line('Process the employee');  
    dbms_output.put_line(p_employee_xml.getCLOBval());

END;


PROCEDURE PROCESS_EMPLOYEES (p_dept_no numeric, p_employees_xml XMLTYPE)
IS
l_number_of_employees int;
l_employee_xml XMLTYPE;
BEGIN

  
  dbms_output.put_line('Process the employees - DEPT NO: ' || p_dept_no);
  dbms_output.put_line(p_employees_xml.getCLOBVal());
  
  
  
  SELECT COUNT(*) INTO l_number_of_employees
  FROM TABLE(xmlsequence(extract(p_employees_xml, '/EMPLOYEES/EMPLOYEE')));

  FOR i in 1..l_number_of_employees LOOP

    SELECT extract (p_employees_xml, '/EMPLOYEES/EMPLOYEE[' || i || ']')
      INTO l_employee_xml
    FROM DUAL;

    PROCESS_EMPLOYEE(l_employee_xml);

  END LOOP;

    
END;

PROCEDURE PROCESS_DEPARTMENT (p_dept_xml XMLTYPE)
IS
l_employees_xml XMLTYPE;
l_dept_no number(8);
BEGIN

  
  dbms_output.put_line('Process the department: ' || p_dept_xml.getCLOBVal());

  -- get the employees and process them
  SELECT l_xml.extract('//DEPARTMENT/EMPLOYEES'),  extractvalue(l_xml,'/DEPARTMENT/DEPTNO')  into l_employees_xml, l_dept_no from dual;
  PROCESS_EMPLOYEES(l_dept_no, l_employees_xml);

END;




BEGIN

  SELECT xml_tools.get_demo_xml(10) INTO l_xml FROM dual;
  
  --
  -- Check a node exists in the document.
  --
  
  l_xpath := '//DEPARTMENT/EMPLOYEES/EMPLOYEE';
  
  IF l_xml.existsnode(l_xpath) = 1 THEN
    dbms_output.put_line('YES');
  ELSE
    dbms_output.put_line('NO');
  END IF;
  
  SELECT extractvalue(l_xml,'/DEPARTMENT/DEPTNO') INTO l_tmp_var
  FROM dual;

  dbms_output.put_line('DEPTNO ' ||l_tmp_var);

  --
  -- Get the XML for the EMPLOYEES
  -- 
  
  --SELECT l_xml.extract(l_xpath) into l_node from dual;
   
  --dbms_output.put_line('EMPLOYEE XML: ' || l_node.getCLOBVal());
  
  --SELECT count(*) INTO l_number_of_emps 
  --FROM TABLE(XMLSequence(extract(l_xml,l_xpath)));
  
  --DBMS_OUTPUT.put_line('Employee Count: ' || l_number_of_emps );
  
  
  SELECT l_xml.extract('//DEPARTMENT') into l_dept_xml from dual;
  PROCESS_DEPARTMENT(l_dept_xml);
  
 -- SELECT l_xml.extract(l_xpath) into l_employees_xml from dual;

 -- PROCESS_EMPLOYEES(l_employees_xml);

END;